I have the following table :
Attribute | Type | Modifier
-----------+--------------------------+----------
motid | integer | not null
objid | integer | not null
date | timestamp with time zone | not null
...with 140 million rows. For each distinct value of
motid there are many rows (with different objid/dates).
I would like to optimize the following query:
=> select * from dico_frs where motid=4742 order by date desc limit 10;
Creating an index on 'date' makes the query use that index:
Limit (cost=0.00..17591.91 rows=10 width=16)
-> Index Scan Backward using dico_frs_date on dico_frs
(cost=0.00..20023641.63 rows=11382 width=16)
But it's still quite slow. I'm thinking an index on (motid, date desc)
would be best
but that doesn't seem to be possible. How can I optimize this query?
--
Eric Cholet
